<templateSet group="sql_snippets">
  <template name="sql_left_join" value="--&#10;SELECT a, b FROM DB.tableOne LEFT JOIN DB.tableTwo ON tableOne.id = tableTwo.id;&#10;--" description="объединение таблиц слева ( А  или (А и Б) ) &lt;{002.png}&gt;" toReformat="true" toShortenFQNames="true">
    <context>
      <option name="SQL" value="true" />
    </context>
  </template>
  <template name="sql_inner_join" value="--&#10;SELECT * FROM TableA A INNER JOIN TableB B ON A.key = B.key&#10;--" description="пересечение таблиц ( А и Б ) &lt;{003.png}&gt;" toReformat="false" toShortenFQNames="true">
    <context>
      <option name="SQL" value="true" />
    </context>
  </template>
  <template name="sql_right_join" value="--&#10;SELECT * FROM TableA A RIGHT JOIN TableB B ON A.key = B.key&#10;--" description="объединение таблиц справа (Б или (А и Б)) &lt;{004.png}&gt;" toReformat="false" toShortenFQNames="true">
    <context>
      <option name="SQL" value="true" />
    </context>
  </template>
  <template name="sql_full_join" value="--&#10;SELECT * FROM TableA A FULL OUTER JOIN TableB B ON A.key = B.key&#10;--" description="полное объединение таблиц ( А или  Б ) &lt;{001.png}&gt;" toReformat="false" toShortenFQNames="true">
    <context>
      <option name="SQL" value="true" />
    </context>
  </template>
  <template name="sql_pirs_join" value="--&#10;SELECT * FROM TableA A FULL OUTER JOIN TableB B ON A.key = B.key&#10;--" description="стрелка пирса ?   !(А и Б)&lt;{005.png}&gt;" toReformat="false" toShortenFQNames="true" />
  <template name="REGEXP_ex1" value="###-------------------------------------&#10;SELECT * from &lt;table.column&gt; AS ee  where ee.&lt;column&gt; REGEXP &quot;^Название стоблца №.*[0-9]$&quot;;&#10;###-------------------------------------" description="example REGEXP 1" toReformat="false" toShortenFQNames="true">
    <context>
      <option name="SQL" value="true" />
    </context>
  </template>
  <template name="sql_add_right_for_dump" value="GRANT USAGE ON *.* TO '&lt;userName&gt;'@'localhost' IDENTIFIED BY '&lt;userpassword&gt;';&#10;GRANT SELECT, LOCK TABLES ON `&lt;dbName&gt;`.* TO '&lt;userName&gt;'@'localhost';&#10;GRANT SELECT, LOCK TABLES, SHOW VIEW, EVENT, TRIGGER ON `&lt;dbName&gt;`.* TO '&lt;userName&gt;'@'localhost';&#10;GRANT TRIGGER ON `reestr`.* TO '&lt;dbName&gt;'@'localhost';" description="дать права пользоватею на использование mysqldump для снятия дампа бд" toReformat="false" toShortenFQNames="true">
    <context>
      <option name="SQL" value="true" />
    </context>
  </template>
  <template name="strict_mode_to_null" value="#to show current settings&#10;SELECT @@GLOBAL.sql_mode;performance_schema&#10;#to set strict mode to null&#10;SET @@GLOBAL.sql_mode=''" description="disable_strict_mode" toReformat="false" toShortenFQNames="true">
    <context>
      <option name="SQL" value="true" />
    </context>
  </template>
  <template name="col" value="$col$ $type$ $null$$END$" description="new column definition" toReformat="true" toShortenFQNames="false">
    <variable name="col" expression="" defaultValue="&quot;col&quot;" alwaysStopAt="true" />
    <variable name="type" expression="" defaultValue="&quot;int&quot;" alwaysStopAt="true" />
    <variable name="null" expression="" defaultValue="&quot;not null&quot;" alwaysStopAt="true" />
    <context>
      <option name="SQL" value="true" />
    </context>
  </template>
  <template name="ins" value="insert into $table$ ($columns$) values ($END$);" description="insert rows into a table" toReformat="true" toShortenFQNames="false">
    <variable name="table" expression="complete()" defaultValue="" alwaysStopAt="true" />
    <variable name="columns" expression="complete()" defaultValue="" alwaysStopAt="true" />
    <context>
      <option name="SQL" value="true" />
    </context>
  </template>
  <template name="sel" value="select * from $table$$END$;" description="select all rows from a table" toReformat="true" toShortenFQNames="false">
    <variable name="table" expression="complete()" defaultValue="" alwaysStopAt="true" />
    <context>
      <option name="SQL" value="true" />
    </context>
  </template>
  <template name="selc" value="select count(*) from $table$ $alias$ where $alias$.$END$;" description="select the number of specific rows in a table" toReformat="true" toShortenFQNames="false">
    <variable name="table" expression="complete()" defaultValue="" alwaysStopAt="true" />
    <variable name="alias" expression="complete()" defaultValue="&quot;alias&quot;" alwaysStopAt="true" />
    <context>
      <option name="SQL" value="true" />
    </context>
  </template>
  <template name="selw" value="select * from $table$ $alias$ where $alias$.$END$;" description="select specific rows from a table" toReformat="true" toShortenFQNames="false">
    <variable name="table" expression="complete()" defaultValue="" alwaysStopAt="true" />
    <variable name="alias" expression="complete()" defaultValue="&quot;alias&quot;" alwaysStopAt="true" />
    <context>
      <option name="SQL" value="true" />
    </context>
  </template>
  <template name="tab" value="create table $table$ (&#10;  $col$ $type$ $null$$END$&#10;);" description="new table definition" toReformat="true" toShortenFQNames="false">
    <variable name="table" expression="" defaultValue="&quot;new_table&quot;" alwaysStopAt="true" />
    <variable name="col" expression="" defaultValue="&quot;col&quot;" alwaysStopAt="true" />
    <variable name="type" expression="" defaultValue="&quot;int&quot;" alwaysStopAt="true" />
    <variable name="null" expression="" defaultValue="&quot;not null&quot;" alwaysStopAt="true" />
    <context>
      <option name="SQL" value="true" />
    </context>
  </template>
  <template name="upd" value="update $table_name$ set $col$ = $value$ where $END$;" description="update values in a table" toReformat="true" toShortenFQNames="false">
    <variable name="table_name" expression="" defaultValue="" alwaysStopAt="true" />
    <variable name="col" expression="complete()" defaultValue="" alwaysStopAt="true" />
    <variable name="value" expression="" defaultValue="" alwaysStopAt="true" />
    <context>
      <option name="SQL" value="true" />
    </context>
  </template>
  <template name="read_from_schema" value="#--------------------------------&#10;select `column_name`, `column_type`, `column_default`, `column_comment`&#10;from `information_schema`.`COLUMNS` &#10;where `table_name` = '$TABLE_NAME$' &#10;and `table_schema` = '$DB_NAME$';&#10;#--------------------------------" description="read information about column type and comment from schema" toReformat="false" toShortenFQNames="true">
    <variable name="TABLE_NAME" expression="" defaultValue="" alwaysStopAt="true" />
    <variable name="DB_NAME" expression="" defaultValue="" alwaysStopAt="true" />
    <context>
      <option name="SQL" value="true" />
    </context>
  </template>
  <template name="sql_truk" value="/*---------------------------------------------------*/&#10;SELECT FIO_1,&#10;&#9;&#9; SUM(if(ee.id_etap = 1,1,0)) AS 'Отчёт #-1',&#10;&#9;&#9; SUM(if(ee.id_etap = 2,1,0)) AS 'Отчёт #-2',&#10;&#9;&#9; SUM(if(ee.id_etap = 3,1,0)) AS 'Отчёт #-3',&#10;&#9;&#9; SUM(if(ee.id_etap = 4,1,0)) AS 'Отчёт #-4',&#10;&#9;&#9; id_etap, id_user, id_work&#10;FROM dbtest.table1 AS ee&#10;INNER JOIN (&#10;SELECT id_user, CONCAT(family,' ',first_name , ' ', second_name, ' ') AS FIO&#10;FROM dbtest.users) AS qq ON ee.id_user = qq.id_user&#10;WHERE ee.id_etap IN (1, 2,  3, 4)&#10;GROUP BY id_user&#10;ORDER BY id_user&#10;/*---------------------------------------------------*/" description="count by diff columns" toReformat="false" toShortenFQNames="true">
    <context>
      <option name="SQL" value="true" />
    </context>
  </template>
</templateSet>